# coding=gbk

from datetime import datetime
from Misc.xlDate import read_xl_date
import win32com.client 
from DataAccess.DBConnFactory import DBConnFactory
import time

from string import Template

CPPIB_PORTF_ID = 'SC032'
		
def CPPIB_trades_import(ref_date, file_path):
	pkedb_conn = DBConnFactory().get_db_connection('PKEDB')
	
	xApp = win32com.client.Dispatch("Excel.Application")
	xBook = xApp.Workbooks.Open(file_path)
	xSht = xBook.Worksheets("Trade Check")
	row = xSht.Range("I65536").End(win32com.client.constants.xlUp).Row + 1

	sql_tpl = Template('''merge into transaction trd
	            using (select TO_DATE('${TRD_DATE}','yyyy-mm-dd') as trade_date,
							  '${TICK}' as ticker,
							  '${TRD_TYPE}' as trade_type,
							  '${PORTF}' as portfolio_id,
							  '${SEC_TYPE}' as security_type,
							  '${AMT}' as amount,
							  '${COST}' as cost_price,
							  '${CURNCY}' as currency,
							  '${MKT}' as market,
							  TO_DATE('${SET_DATE}','yyyy-mm-dd') as settle_date
					    from dual) t
				on (trd.trade_date=t.trade_date and trd.ticker=t.ticker and trd.portfolio_id=t.portfolio_id and trd.security_type=t.security_type)
				when matched then update set
					trd.trade_type=t.trade_type,
					trd.amount=t.amount,
					trd.cost_price=t.cost_price,
					trd.currency=t.currency,
					trd.market=t.market,
					trd.settle_date=t.settle_date
				when not matched then insert (trd.trade_date,trd.ticker,trd.trade_type,trd.portfolio_id,trd.security_type,trd.amount,trd.cost_price,trd.currency,trd.market,trd.settle_date)
					values(t.trade_date,t.ticker,t.trade_type,t.portfolio_id,t.security_type,t.amount,t.cost_price,t.currency,t.market,t.settle_date)''')

	for i in range(3, row):	
		
		trade_date = read_xl_date(float(xSht.Cells(i, 9).Value))
		
		if trade_date == ref_date:
		
			ticker = xSht.Cells(i, 10).Value
			trade_type = xSht.Cells(i, 11).Value
			amount = xSht.Cells(i, 12).Value
			cost_price = xSht.Cells(i, 13).Value
			currency = xSht.Cells(i, 14).Value
			security_type = xSht.Cells(i, 15).Value
			market = ''
			settle_date = read_xl_date(float(xSht.Cells(i, 16).Value)) if xSht.Cells(i, 16).Value else trade_date
	
			sql_text=sql_tpl.substitute(TRD_DATE = trade_date.strftime('%Y-%m-%d'), 
										TICK = ticker, 
										TRD_TYPE = trade_type,
										PORTF = CPPIB_PORTF_ID,
										SEC_TYPE = security_type,
										AMT = amount,
										COST = cost_price,
										CURNCY = currency,
										MKT = market,
										SET_DATE = settle_date.strftime('%Y-%m-%d'))
			pkedb_conn.cursor().execute(sql_text)
							
		pkedb_conn.commit()	
	
	xBook.Close(SaveChanges = 0)
	del xApp
		
	print 'info, Step 1.6: CPPIB trades import completed.'